﻿using AutoMapper;
using Dapper;
using DocumentFormat.OpenXml.Spreadsheet;
using Hangfire.LiteDB;
using housingsafety.server.Caching.Core;
using housingsafety.server.Entities;
using housingsafety.server.Entities.DataModels;
using housingsafety.server.FastDBF;
using housingsafety.server.Helpers;
using housingsafety.server.Models;
using housingsafety.server.Repository;
using housingsafety.server.Repository.Extensions;
using housingsafety.server.Svcs;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.WebUtilities;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Microsoft.Net.Http.Headers;
using MySql.Data.MySqlClient;
using NetTopologySuite.Geometries;
using NetTopologySuite.IO;
using Newtonsoft.Json;
using Npgsql;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using Quartz.Util;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.IO;
using System.Linq;
using System.Net;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Transactions;

namespace housingsafety.server.Controllers
{
    /// <summary>
    /// 村庄管理服务
    /// </summary>
    public class VillageMngController : BaseController<VillageMngController>
    {
        private readonly ICache _cache;
        private readonly IMapper _mapper;
        private readonly IStorageProvider _storageProvider;
        private readonly ZmDbContext _dbContext;
        private readonly IRepository<ZmUser> _userRepository;
        private readonly IRepository<ZmBuildingsLayer> _buildingsRepository;
        private readonly IRepository<ZmFileAttach> _fileAttachRepository;
        private readonly IRepository<ZmVillageFireSafety> _villageFireSafetyRepository;

        public VillageMngController(IStorageProvider storageProvider
            , IRepository<ZmUser> userRepository
            , IRepository<ZmBuildingsLayer> buildingsRepository
            , IRepository<ZmFileAttach> fileAttachRepository
            , IRepository<ZmVillageFireSafety> villageFireSafetyRepository
            , IMapper mapper, ICacheProvider cacheProvider, ZmDbContext dbContext
            , ILogger<VillageMngController> logger
            , IRepository<ZmSystemLog> systemLogRepository) : base(logger, systemLogRepository)
        {
            _storageProvider = storageProvider;
            _mapper = mapper;
            _dbContext = dbContext;
            _cache = cacheProvider.CreateCache();

            _userRepository = userRepository;
            _buildingsRepository = buildingsRepository;
            _fileAttachRepository = fileAttachRepository;
            _villageFireSafetyRepository = villageFireSafetyRepository;
        }

        /// <summary>
        /// 提交村庄排查完成
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [HttpPost("villages/{id}/submit")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet), 200)]
        public RespRet PlaceResidentSubmit([FromRoute] string id)
        {
            if (!ModelState.IsValid)
            {
                return new RespRet(false, "Invalid Request");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet(false, "user not logined");
            }

            var data = _villageFireSafetyRepository.FirstOrDefault(_ => _.Id == id && _.IsDelete != 1);
            if (null == data)
            {
                return new RespRet(false, "不存在");
            }

            data.Pczt = 2;
            _villageFireSafetyRepository.Update(data);
            _villageFireSafetyRepository.SaveChanges();
            return new RespRet(true, "Ok");
        }

        #region 村庄调查

        /// <summary>
        /// 动态矢量瓦片服务(支持编辑)
        /// </summary>
        /// <param name="x"></param>
        /// <param name="y"></param>
        /// <param name="z"></param>
        /// <returns></returns>
        [HttpGet("villages/dynamic/vector/tiles")]
        public ActionResult GetIntelLayerVt([FromQuery] int x, [FromQuery] int y, [FromQuery] int z)
        {
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return BadRequest("user not login");
            }

            var tile = GetDynamicVectorTile(z, x, y);
            return File(tile, "application/vnd.mapbox-vector-tile");
        }
        public class village
        {
            public IFormFile file { get; set; }
            public object fileList { get; set; }
        }

        /// <summary>
        /// 导入
        /// </summary>
        /// <returns></returns>
        [HttpPost("villages/dynamic/UploadFile")]
        public Object UploadFile([FromForm] IFormFile file)
        {
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<string>(false, "user not logined");
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<string>(false, "用户名不存在");
            }
            // 上传后的文件
            // var filePost = village;
            var filePost = file;
            // 文件名
            string fileName = filePost.FileName;
            var guid = Guid.NewGuid().ToString("N");
            // 服务器上文件要保存的路径
            var today = DateTime.Today.ToString().Replace("0:00:00", "").Replace("/", "-");
            string TruePath = Directory.GetCurrentDirectory() + "/wwwroot/files/" + today + "/" + guid + Path.GetExtension(fileName);
            if (!Directory.Exists(Directory.GetCurrentDirectory() + "/wwwroot/files/" + today))
            {
                Directory.CreateDirectory(Directory.GetCurrentDirectory() + "/wwwroot/files/" + today);
            }
            // 保存文件sy
            using (FileStream fs = System.IO.File.Create(TruePath.Replace(" ", "")))
            {
                filePost.CopyTo(fs);
            }
            Stream stream = filePost.OpenReadStream();
            XSSFWorkbook workbook = new XSSFWorkbook(stream);//创建工作薄，并读取文件流
            XSSFSheet sheet = (XSSFSheet)workbook.GetSheetAt(0);//获取第一个sheet
            var tmpCount = sheet.LastRowNum;

            XSSFRow title = (XSSFRow)sheet.GetRow(0);
            if (title.Cells[0].RichStringCellValue.ToString().Replace(" ", "") != "区县")
            {
                return new RespRet<string>(false, "第一列列名应为区县");
            }
            if (title.Cells[1].RichStringCellValue.ToString().Replace(" ", "") != "街镇")
            {
                return new RespRet<string>(false, "第二列列名应为街镇");
            }
            if (title.Cells[2].RichStringCellValue.ToString().Replace(" ", "") != "村庄")
            {
                return new RespRet<string>(false, "第三列列名应为村庄");
            }
            if (title.Cells[3].RichStringCellValue.ToString().Replace(" ", "") != "编码")
            {
                return new RespRet<string>(false, "第四列列名应为编码");
            }
            if (title.Cells[4].RichStringCellValue.ToString().Replace(" ", "") != "村域内的电动自行车数量(辆)")
            {
                return new RespRet<string>(false, "第五列列名应为村域内的电动自行车数量(辆)");
            }
            if (title.Cells[5].RichStringCellValue.ToString().Replace(" ", "") != "村域内村民自建出租房的建筑数(栋)")
            {
                return new RespRet<string>(false, "第六列列名应为村域内村民自建出租房的建筑数(栋)");
            }
            if (title.Cells[6].RichStringCellValue.ToString().Replace(" ", "") != "消防车道数量")
            {
                return new RespRet<string>(false, "第七列列名应为消防车道数量");
            }
            if (title.Cells[7].RichStringCellValue.ToString().Replace(" ", "") != "消防水池数量")
            {
                return new RespRet<string>(false, "第八列列名应为消防水池数量");

            }
            if (title.Cells[8].RichStringCellValue.ToString().Replace(" ", "") != "室外消火栓数量")
            {
                return new RespRet<string>(false, "第九列列名应为室外消火栓数量");
            }
            if (title.Cells[9].RichStringCellValue.ToString().Replace(" ", "") != "消防水鹤数量")
            {
                return new RespRet<string>(false, "第十列列名应为消防水鹤数量");
            }

            if (title.Cells[10].RichStringCellValue.ToString().Replace(" ", "") != "电动自行车充电设施数量")
            {
                return new RespRet<string>(false, "第十一列列名应为电动自行车充电设施数量");
            }
            if (title.Cells[11].RichStringCellValue.ToString().Replace(" ", "") != "专职消防队数量")
            {
                return new RespRet<string>(false, "第十二列列名应为专职消防队数量");
            }
            if (title.Cells[12].RichStringCellValue.ToString().Replace(" ", "") != "专职消防队员数量")
            {
                return new RespRet<string>(false, "第十三列列名应为专职消防队员数量");
            }
            if (title.Cells[13].RichStringCellValue.ToString().Replace(" ", "") != "水罐消防车数量")
            {
                return new RespRet<string>(false, "第十四列列名应为水罐消防车数量");
            }
            if (title.Cells[14].RichStringCellValue.ToString().Replace(" ", "") != "微型消防站数量")
            {
                return new RespRet<string>(false, "第十五列列名应为微型消防站数量");
            }
            if (title.Cells[15].RichStringCellValue.ToString().Replace(" ", "") != "微型消防站队员数量")
            {
                return new RespRet<string>(false, "第十六列列名应为微型消防站队员数量");
            }
            if (title.Cells[16].RichStringCellValue.ToString().Replace(" ", "") != "微型消防站消防车数量")
            {
                return new RespRet<string>(false, "第十七列列名应为微型消防站消防车数量");
            }


            for (int i = 1; i <= tmpCount; i++)
            {
                XSSFRow row = (XSSFRow)sheet.GetRow(i);//根据下标获取行数据


                if (row.GetCell(0) == null || string.IsNullOrEmpty(row.GetCell(0).ToString()))
                {
                    return new RespRet<string>(false, $"第一列第{i+1}行不能为空");
                }
                if (row.GetCell(1) == null || string.IsNullOrEmpty(row.GetCell(1).ToString()))
                {
                    return new RespRet<string>(false, $"第二列第{i+1}行不能为空");
                }
                if (row.GetCell(2) == null || string.IsNullOrEmpty(row.GetCell(2).ToString()))
                {
                    return new RespRet<string>(false, $"第三列第{i+1}行不能为空");
                }
                if (row.GetCell(3) == null || string.IsNullOrEmpty(row.GetCell(3).ToString()))
                {
                    return new RespRet<string>(false, $"第四列第{i+1}行不能为空");
                }
                if (row.GetCell(4) == null|| string.IsNullOrEmpty(row.GetCell(4).ToString()))
                {
                    return new RespRet<string>(false, $"第五列第{i+1}行不能为空");
                }
                if (row.GetCell(5) == null || string.IsNullOrEmpty(row.GetCell(5).ToString()))
                {
                    return new RespRet<string>(false, $"第六列第{i+1}行不能为空");
                }
                if (row.GetCell(6) == null || string.IsNullOrEmpty(row.GetCell(6).ToString()))
                {
                    return new RespRet<string>(false, $"第七列第{i+1}行不能为空");
                }
                if (row.GetCell(7) == null || string.IsNullOrEmpty(row.GetCell(7).ToString()))
                {
                    return new RespRet<string>(false, $"第八列第{i+1}行不能为空");

                }
                if (row.GetCell(8) == null || string.IsNullOrEmpty(row.GetCell(8).ToString()))
                {
                    return new RespRet<string>(false, $"第九列第{i+1}行不能为空");
                }
                if (row.GetCell(9) == null || string.IsNullOrEmpty(row.GetCell(9).ToString()))
                {
                    return new RespRet<string>(false, $"第十列第{i+1}行不能为空");
                }

                if (row.GetCell(10) == null || string.IsNullOrEmpty(row.GetCell(10).ToString()))
                {
                    return new RespRet<string>(false, $"第十一列第{i+1}行不能为空");
                }
                if (row.GetCell(11) == null || string.IsNullOrEmpty(row.GetCell(11).ToString()))
                {
                    return new RespRet<string>(false, $"第十二列第{i+1}行不能为空");
                }
                if (row.GetCell(12) == null || string.IsNullOrEmpty(row.GetCell(12).ToString()))
                {
                    return new RespRet<string>(false, $"第十三列第{i+1}行不能为空");
                }
                if (row.GetCell(13) == null || string.IsNullOrEmpty(row.GetCell(13).ToString()))
                {
                    return new RespRet<string>(false, $"第十四列第{i+1}行不能为空");
                }
                if (row.GetCell(14) == null || string.IsNullOrEmpty(row.GetCell(14).ToString()))
                {
                    return new RespRet<string>(false, $"第十五列第{i+1}行不能为空");
                }
                if (row.GetCell(15) == null || string.IsNullOrEmpty(row.GetCell(15).ToString()))
                {
                    return new RespRet<string>(false, $"第十六列第{i+1}行不能为空");
                }
                if (row.GetCell(16) == null || string.IsNullOrEmpty(row.GetCell(16).ToString()))
                {
                    return new RespRet<string>(false, $"第十七列第{i+1}行不能为空");
                }



                //编码
                var VillageOwnership = row.Cells[3].RichStringCellValue.ToString();
                if(string.IsNullOrWhiteSpace(VillageOwnership)|| VillageOwnership.Length != 12)
                {
                    return new RespRet<string>(false, $"第{i+1}行编码格式不正确");
                }
                //村庄
                var VillageName = row.Cells[2].RichStringCellValue.ToString();
                if (string.IsNullOrWhiteSpace(VillageName) )
                {
                    return new RespRet<string>(false, $"第{i + 1}行村庄不能为空");
                }
                //村域内的电动自行车数量（辆）
                int ElectricBicycleNumber;
                bool isNumeric = int.TryParse(row.Cells[4].ToString(), out ElectricBicycleNumber);

                if (isNumeric)
                {
                    if (ElectricBicycleNumber < 0)
                    {
                        return new RespRet<string>(false, $"第{i + 1}行村域内的电动自行车数量（辆）类型不正确");
                    }
                }
                else
                {
                  return new RespRet<string>(false, $"第{i + 1}行村域内的电动自行车数量（辆）类型不正确");
                }



                //村域内村民自建出租房的建筑数(栋)
                int self_built_rental_house_number;
                bool isNumeric5 = int.TryParse(row.Cells[5].ToString(), out self_built_rental_house_number);

                if (isNumeric5)
                {
                    if (self_built_rental_house_number < 0)
                    {
                        return new RespRet<string>(false, $"第{i + 1}行村域内村民自建出租房的建筑数(栋)类型不正确");
                    }
                }
                else
                {
                    return new RespRet<string>(false, $"第{i + 1}行村域内村民自建出租房的建筑数(栋)类型不正确");
                }
                //消防车车道数量
                int FireLanesNumber;
                bool isNumeric6 = int.TryParse(row.Cells[6].ToString(), out FireLanesNumber);

                if (isNumeric6)
                {
                    if (FireLanesNumber < 0)
                    {
                        return new RespRet<string>(false, $"第{i + 1}行消防车道数量类型不正确");
                    }
                }
                else
                {
                    return new RespRet<string>(false, $"第{i + 1}行消防车道数量类型不正确");
                }

                int FirePoolNumber;
                bool isNumeric7 = int.TryParse(row.Cells[7].ToString(), out FirePoolNumber);

                if (isNumeric7)
                {
                    if (FirePoolNumber < 0)
                    {
                        return new RespRet<string>(false, $"第{i + 1}行消防水池数量类型不正确");
                    }
                }
                else
                {
                    return new RespRet<string>(false, $"第{i + 1}行消防水池数量类型不正确");
                }

                int OutdoorFireHydrantNumber;
                bool isNumeric8 = int.TryParse(row.Cells[8].ToString(), out OutdoorFireHydrantNumber);

                if (isNumeric8)
                {
                    if (OutdoorFireHydrantNumber < 0)
                    {
                        return new RespRet<string>(false, $"第{i + 1}行室外消火栓数量类型不正确");
                    }
                }
                else
                {
                    return new RespRet<string>(false, $"第{i + 1}行室外消火栓数量类型不正确");
                }

                int FireWaterCraneNumber;
                bool isNumeric9 = int.TryParse(row.Cells[9].ToString(), out FireWaterCraneNumber);

                if (isNumeric9)
                {
                    if (FireWaterCraneNumber < 0)
                    {
                        return new RespRet<string>(false, $"第{i + 1}行消防水鹤数量类型不正确");
                    }
                }
                else
                {
                    return new RespRet<string>(false, $"第{i + 1}行消防水鹤数量类型不正确");
                }


                int ChargingFacilitiesNumber;
                bool isNumeric10 = int.TryParse(row.Cells[10].ToString(), out ChargingFacilitiesNumber);

                if (isNumeric10)
                {
                    if (ChargingFacilitiesNumber < 0)
                    {
                        return new RespRet<string>(false, $"第{i + 1}行电动自行车充电设施数量类型不正确");
                    }
                }
                else
                {
                    return new RespRet<string>(false, $"第{i + 1}行电动自行车充电设施数量类型不正确");
                }

                int FireBrigadeNumber;
                bool isNumeric11 = int.TryParse(row.Cells[11].ToString(), out FireBrigadeNumber);

                if (isNumeric11)
                {
                    if (FireBrigadeNumber < 0)
                    {
                        return new RespRet<string>(false, $"第{i + 1}行专职消防队数量类型不正确");
                    }
                }
                else
                {
                    return new RespRet<string>(false, $"第{i + 1}行专职消防队数量类型不正确");
                }


                int FireFighterNumber;
                bool isNumeric12 = int.TryParse(row.Cells[12].ToString(), out FireFighterNumber);

                if (isNumeric12)
                {
                    if (FireFighterNumber < 0)
                    {
                        return new RespRet<string>(false, $"第{i + 1}行专职消防队员数量类型不正确");
                    }
                }
                else
                {
                    return new RespRet<string>(false, $"第{i + 1}行专职消防队员数量类型不正确");
                }


                int FireTruckNumber;
                bool isNumeric13 = int.TryParse(row.Cells[13].ToString(), out FireTruckNumber);

                if (isNumeric13)
                {
                    if (FireTruckNumber < 0)
                    {
                        return new RespRet<string>(false, $"第{i + 1}行水罐消防车数量类型不正确");
                    }
                }
                else
                {
                    return new RespRet<string>(false, $"第{i + 1}行水罐消防车数量类型不正确");
                }

                int FireStationNumber;
                bool isNumeric14 = int.TryParse(row.Cells[14].ToString(), out FireStationNumber);

                if (isNumeric14)
                {
                    if (FireStationNumber < 0)
                    {
                        return new RespRet<string>(false, $"第{i + 1}行微型消防站数量类型不正确");
                    }
                }
                else
                {
                    return new RespRet<string>(false, $"第{i + 1}行微型消防站数量类型不正确");
                }

                int FireStationPersonnelNumber;
                bool isNumeric15 = int.TryParse(row.Cells[15].ToString(), out FireStationPersonnelNumber);

                if (isNumeric15)
                {
                    if (FireStationPersonnelNumber < 0)
                    {
                        return new RespRet<string>(false, $"第{i + 1}行微型消防站队员数量类型不正确");
                    }
                }
                else
                {
                    return new RespRet<string>(false, $"第{i + 1}行微型消防站队员数量类型不正确");
                }


                int FireStationTruckNumber;
                bool isNumeric16 = int.TryParse(row.Cells[16].ToString(), out FireStationTruckNumber);

                if (isNumeric16)
                {
                    if (FireStationTruckNumber < 0)
                    {
                        return new RespRet<string>(false, $"第{i + 1}行微型消防站消防车数量类型不正确");
                    }
                }
                else
                {
                    return new RespRet<string>(false, $"第{i + 1}行微型消防站消防车数量类型不正确");
                }

                var zm = new ZmVillageFireSafetyYJ
                {
                    Id = Guid.NewGuid().ToString("N"),
                    VillageOwnership = VillageOwnership,
                    ElectricBicycleNumber = ElectricBicycleNumber,
                    VillageName = VillageName,
                    SelfBuiltRentalHouseNumber = self_built_rental_house_number,
                   // SelfBuiltRentalHouseNumber = SelfBuiltRentalHouseNumber,
                    FireLanesNumber = FireLanesNumber,
                    FirePoolNumber = FirePoolNumber,
                    OutdoorFireHydrantNumber = OutdoorFireHydrantNumber,
                    FireWaterCraneNumber = FireWaterCraneNumber,
                    ChargingFacilitiesNumber = ChargingFacilitiesNumber,
                    FireBrigadeNumber = FireBrigadeNumber,
                    FireFighterNumber = FireFighterNumber,
                    FireTruckNumber = FireTruckNumber,
                    FireStationNumber = FireStationNumber,
                    FireStationPersonnelNumber = FireStationPersonnelNumber,
                    FireStationTruckNumber = FireStationTruckNumber,
                    CreateBy = user.Number,
                    CreateOn = DateTime.Now,
                    Village = VillageOwnership,
                    Province = "100000",
                    City = "110000",
                    County = VillageOwnership.Substring(0, 6),
                    Country = VillageOwnership.Substring(0, 9) + "000",
                    Pczt = 0

                };
                var cz = _dbContext.ZmVillageFireSafetyYJ.FirstOrDefault(c => c.Village == zm.Village && c.IsDelete == null);
                if (cz != null)
                {
                    zm.Id = cz.Id;
                    zm.Pczt = cz.Pczt;
                    zm.CreateBy = cz.CreateBy;
                    zm.CreateOn = cz.CreateOn;
                    zm.ModifiedBy = user.Number;
                    zm.ModifiedOn = DateTime.Now;
                    zm.FireLayoutPhoto = cz.FireLayoutPhoto;
                    zm.star_rating_report = cz.star_rating_report;
                    _dbContext.Entry(cz).State = EntityState.Detached; // 将实体从上下文中分离
                    _dbContext.Entry(zm).State = EntityState.Modified; // 将更新后的实体附加到上下文中
                }

                else
                {
                    _dbContext.ZmVillageFireSafetyYJ.Add(zm);
                }
            }
            _dbContext.SaveChanges();
            stream.Dispose();
            workbook.Clear();
            // 返回文件在服务器上的路径
            return new RespRet<string>(true, "ok", "/files/" + guid + Path.GetExtension(fileName));

        }

        /// <summary>
        /// 获取村庄属性
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("villages/search-attr")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<Page<VillageFireSafetyAttrDto>>), 200)]
        public RespRet<Page<VillageFireSafetyAttrDto>> SearchAttrList([FromQuery] PagePcztRequestInput pageRequest)
        {
            if (pageRequest.PageIndex < 0)
            {
                return new RespRet<Page<VillageFireSafetyAttrDto>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<VillageFireSafetyAttrDto>>(false, "user not logined");
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<VillageFireSafetyAttrDto>>(false, "用户名不存在");
            }



            var sbSql = new StringBuilder();
            var sbSqlWhere = new StringBuilder();
            var sqlParams = new List<NpgsqlParameter>();
            sbSql.Append($@"SELECT * FROM zm_village_fire_safety where (is_delete<>1 or is_delete is null) ");

            //var sqlCount = new StringBuilder();
            //var sqlCountWhere = new StringBuilder();
            //var sqlCountParams = new List<NpgsqlParameter>();
            //sqlCount.Append($@"SELECT id FROM zm_village_fire_safety where (is_delete<>1 or is_delete is null) ");

            var codeWhere = Utilitys.GetLikeSqlWhereVillage(user.Jurisdiction, null);
            if (!string.IsNullOrEmpty(codeWhere))
            {
                sbSqlWhere.Append($"{codeWhere} and");
            }

            if (!string.IsNullOrEmpty(pageRequest.Text))
            {
                sbSqlWhere.Append($@" village_name like @village_name and");
                sqlParams.Add(new NpgsqlParameter("village_name", $"%{pageRequest.Text}%"));
            }

            if (pageRequest.Pczt.HasValue)
            {
                sbSqlWhere.Append($@" pczt = @pczt and");
                sqlParams.Add(new NpgsqlParameter("pczt", pageRequest.Pczt.Value));
            }

            if (!string.IsNullOrEmpty(sbSqlWhere.ToString()))
            {
                sbSql.Append($@" and {sbSqlWhere} 1=1 ");
            }

            var count = _dbContext.ZmVillageFireSafety
                .FromSqlRaw(sbSql.ToString(), sqlParams.ToArray()).Count();

            sbSql.Append($@" order by village limit @limit offset @offset");
            sqlParams.Add(new NpgsqlParameter("limit", pageRequest.Limit));
            sqlParams.Add(new NpgsqlParameter("offset", pageRequest.Limit * pageRequest.PageIndex));

            var datas = _dbContext.ZmVillageFireSafety.FromSqlRaw(sbSql.ToString(), sqlParams.ToArray())
                    .Select(_ => _mapper.Map<VillageFireSafetyAttrDto>(_)).ToList();

            var pageData = new Page<VillageFireSafetyAttrDto>(datas
                , pageRequest.PageIndex + 1, pageRequest.Limit, count);
            return new RespRet<Page<VillageFireSafetyAttrDto>>(true, "Ok", pageData);
        }

        [HttpPost("villages/search-attr")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<Page<VillageFireSafetyAttrDto>>), 200)]
        public RespRet<Page<VillageFireSafetyAttrDto>> SearchAttrList2([FromBody] PageVillagePcztRequestInput pageRequest)
        {
            if (pageRequest.PageIndex < 0)
            {
                return new RespRet<Page<VillageFireSafetyAttrDto>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<VillageFireSafetyAttrDto>>(false, "user not logined");
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<VillageFireSafetyAttrDto>>(false, "用户名不存在");
            }



            string strc = " and 1=1  ";
            if (pageRequest.check777 == 777)
            {
                strc = " and village_ownership in(select region_code from zm_department_xf_2023 where check777=1 and regional_level='7')   ";
            }
            else if (pageRequest.check777 == 108)
            {
                strc = " and village_ownership in(select region_code from zm_department_xf_2023 where check108=1 and regional_level='7')   ";
            }

            var sbSql = new StringBuilder();
            var sbSqlWhere = new StringBuilder();
            var sqlParams = new List<NpgsqlParameter>();
            sbSql.Append($@"SELECT * FROM zm_village_fire_safety where (is_delete<>1 or is_delete is null) " + strc);

            var codeWhere = Utilitys.GetLikeSqlWhereVillage(user.Jurisdiction, null);
            if (!string.IsNullOrEmpty(codeWhere))
            {
                sbSqlWhere.Append($"{codeWhere} and");
            }

            if (pageRequest.Districts != null)
            {
                var districtCodeWhere = Utilitys.GetLikeSqlWhereVillage(JsonConvert.SerializeObject(pageRequest.Districts), null);
                if (!string.IsNullOrEmpty(districtCodeWhere))
                {
                    sbSqlWhere.Append($"{districtCodeWhere} and");
                }
            }

            if (!string.IsNullOrEmpty(pageRequest.Text))
            {
                sbSqlWhere.Append($@" village_name like @village_name and");
                sqlParams.Add(new NpgsqlParameter("village_name", $"%{pageRequest.Text}%"));
            }

            if (pageRequest.Pczt.HasValue)
            {
                sbSqlWhere.Append($@" pczt = @pczt and");
                sqlParams.Add(new NpgsqlParameter("pczt", pageRequest.Pczt.Value));
            }

            if (!string.IsNullOrEmpty(sbSqlWhere.ToString()))
            {
                sbSql.Append($@" and {sbSqlWhere} 1=1 ");
            }

            var count = _dbContext.ZmVillageFireSafety
                .FromSqlRaw(sbSql.ToString(), sqlParams.ToArray()).Count();

            sbSql.Append($@" order by village limit @limit offset @offset");
            sqlParams.Add(new NpgsqlParameter("limit", pageRequest.Limit));
            sqlParams.Add(new NpgsqlParameter("offset", pageRequest.Limit * pageRequest.PageIndex));

            var datas = _dbContext.ZmVillageFireSafety.FromSqlRaw(sbSql.ToString(), sqlParams.ToArray())
                    .Select(_ => _mapper.Map<VillageFireSafetyAttrDto>(_)).ToList();

            foreach (var item in datas)
            {
                item.xzqdmname = _dbContext.Database.GetDbConnection().Query<string>($@"select region_name from zm_department where region_code='{item.County}'").FirstOrDefault();
                item.townname = _dbContext.Database.GetDbConnection().Query<string>($@"select region_name from zm_department where region_code='{item.Country}'").FirstOrDefault();
            }
            var pageData = new Page<VillageFireSafetyAttrDto>(datas
                , pageRequest.PageIndex + 1, pageRequest.Limit, count);
            return new RespRet<Page<VillageFireSafetyAttrDto>>(true, "Ok", pageData);
        }



        [HttpPost("villages/search-attryj")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<Page<VillageFireSafetyAttrDto>>), 200)]
        public RespRet<Page<VillageFireSafetyAttrDto>> SearchAttrList2Yj([FromBody] PageVillagePcztRequestInput pageRequest)
        {
            if (pageRequest.PageIndex < 0)
            {
                return new RespRet<Page<VillageFireSafetyAttrDto>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<VillageFireSafetyAttrDto>>(false, "user not logined");
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<VillageFireSafetyAttrDto>>(false, "用户名不存在");
            }



            string strc = " and 1=1  ";
            if (pageRequest.check777 == 777)
            {
                strc = " and village_ownership in(select region_code from zm_department_xf_2023 where check777=1 and regional_level='7')   ";
            }
            else if (pageRequest.check777 == 108)
            {
                strc = " and village_ownership in(select region_code from zm_department_xf_2023 where check108=1 and regional_level='7')   ";
            }

            var sbSql = new StringBuilder();
            var sbSqlWhere = new StringBuilder();
            var sqlParams = new List<NpgsqlParameter>();
            sbSql.Append($@"SELECT * FROM zm_village_fire_safety_yj where (is_delete<>1 or is_delete is null) " + strc);

            var codeWhere = Utilitys.GetLikeSqlWhereVillage(user.Jurisdiction, null);
            if (!string.IsNullOrEmpty(codeWhere))
            {
                sbSqlWhere.Append($"{codeWhere} and");
            }

            if (pageRequest.Districts != null)
            {
                var districtCodeWhere = Utilitys.GetLikeSqlWhereVillage(JsonConvert.SerializeObject(pageRequest.Districts), null);
                if (!string.IsNullOrEmpty(districtCodeWhere))
                {
                    sbSqlWhere.Append($"{districtCodeWhere} and");
                }
            }

            if (!string.IsNullOrEmpty(pageRequest.Text))
            {
                sbSqlWhere.Append($@" village_name like @village_name and");
                sqlParams.Add(new NpgsqlParameter("village_name", $"%{pageRequest.Text}%"));
            }

            if (pageRequest.Pczt.HasValue)
            {
                sbSqlWhere.Append($@" pczt = @pczt and");
                sqlParams.Add(new NpgsqlParameter("pczt", pageRequest.Pczt.Value));
            }

            if (!string.IsNullOrEmpty(sbSqlWhere.ToString()))
            {
                sbSql.Append($@" and {sbSqlWhere} 1=1 ");
            }

            var count = _dbContext.ZmVillageFireSafety
                .FromSqlRaw(sbSql.ToString(), sqlParams.ToArray()).Count();

            sbSql.Append($@" order by  village limit @limit offset @offset");
            sqlParams.Add(new NpgsqlParameter("limit", pageRequest.Limit));
            sqlParams.Add(new NpgsqlParameter("offset", pageRequest.Limit * pageRequest.PageIndex));

            var datas = _dbContext.ZmVillageFireSafetyYJ.FromSqlRaw(sbSql.ToString(), sqlParams.ToArray())
                    .Select(_ => _mapper.Map<VillageFireSafetyAttrDto>(_)).ToList();

            foreach (var item in datas)
            {
                item.xzqdmname = _dbContext.Database.GetDbConnection().Query<string>($@"select region_name from zm_department where region_code='{item.County}'").FirstOrDefault();
                item.townname = _dbContext.Database.GetDbConnection().Query<string>($@"select region_name from zm_department where region_code='{item.Country}'").FirstOrDefault();
            }
            var pageData = new Page<VillageFireSafetyAttrDto>(datas
                , pageRequest.PageIndex + 1, pageRequest.Limit, count);
            return new RespRet<Page<VillageFireSafetyAttrDto>>(true, "Ok", pageData);
        }




        /// <summary>
        /// 315村庄
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpPost("villages/search-attr_314")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<Page<VillageFireSafetyAttrDto>>), 200)]
        public RespRet<Page<VillageFireSafetyAttrDto>> SearchAttrList2_314([FromBody] PageVillagePcztRequestInput pageRequest)
        {
            if (pageRequest.PageIndex < 0)
            {
                return new RespRet<Page<VillageFireSafetyAttrDto>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<VillageFireSafetyAttrDto>>(false, "user not logined");
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<VillageFireSafetyAttrDto>>(false, "用户名不存在");
            }

            //if (pageRequest.type > -1 || pageRequest.check315 > -1)
            //{
            //    return new RespRet<Page<VillageFireSafetyAttrDto>>(false, "参数错误type、check315");
            //}


            var sbSql = new StringBuilder();
            var sbSqlWhere = new StringBuilder();
            var sqlParams = new List<NpgsqlParameter>();
            string strc = " 1=1 and ";
            if (pageRequest.type == 777)
            {
                strc = "village_ownership in(select region_code from zm_department where check777=1 and regional_level='7') and  ";
            }
            else if (pageRequest.type == 107)
            {
                strc = "village_ownership in(select region_code from zm_department where check107=1 and regional_level='7') and  ";
            }

            if (pageRequest.check315 == 0)
            {
                sbSql.Append($@"SELECT * FROM zm_village_fire_safety_314 where " + strc + " (modified_on <= '2023-3-15' or create_on <= '2023-3-15') and (is_delete<>1 or is_delete is null) ");
            }
            else if (pageRequest.check315 == 1)
            {
                sbSql.Append($@"SELECT * FROM zm_village_fire_safety where  " + strc + "   (modified_on > '2023-3-15' or create_on > '2023-3-15') and (is_delete<>1 or is_delete is null) ");
            }
            var codeWhere = Utilitys.GetLikeSqlWhereVillage(user.Jurisdiction, null);
            if (!string.IsNullOrEmpty(codeWhere))
            {
                sbSqlWhere.Append($"{codeWhere} and");
            }

            if (pageRequest.Districts != null)
            {
                var districtCodeWhere = Utilitys.GetLikeSqlWhereVillage(JsonConvert.SerializeObject(pageRequest.Districts), null);
                if (!string.IsNullOrEmpty(districtCodeWhere))
                {
                    sbSqlWhere.Append($"{districtCodeWhere} and");
                }
            }

            if (!string.IsNullOrEmpty(pageRequest.Text))
            {
                sbSqlWhere.Append($@" village_name like @village_name and");
                sqlParams.Add(new NpgsqlParameter("village_name", $"%{pageRequest.Text}%"));
            }

            if (pageRequest.Pczt.HasValue)
            {
                sbSqlWhere.Append($@" pczt = @pczt and");
                sqlParams.Add(new NpgsqlParameter("pczt", pageRequest.Pczt.Value));
            }

            if (!string.IsNullOrEmpty(sbSqlWhere.ToString()))
            {
                sbSql.Append($@" and {sbSqlWhere} 1=1 ");
            }

            var count = _dbContext.ZmVillageFireSafety
                .FromSqlRaw(sbSql.ToString(), sqlParams.ToArray()).Count();
            //写入缓存
            var list = _dbContext.ZmVillageFireSafety
                .FromSqlRaw(sbSql.ToString(), sqlParams.ToArray());
            _cache.Set("search-attr_314" + userNumber, list);
            sbSql.Append($@" limit @limit offset @offset");
            sqlParams.Add(new NpgsqlParameter("limit", pageRequest.Limit));
            sqlParams.Add(new NpgsqlParameter("offset", pageRequest.Limit * pageRequest.PageIndex));

            var datas = _dbContext.ZmVillageFireSafety.FromSqlRaw(sbSql.ToString(), sqlParams.ToArray())
                    .Select(_ => _mapper.Map<VillageFireSafetyAttrDto>(_)).ToList();

            var pageData = new Page<VillageFireSafetyAttrDto>(datas
                , pageRequest.PageIndex + 1, pageRequest.Limit, count);
            return new RespRet<Page<VillageFireSafetyAttrDto>>(true, "Ok", pageData);
        }


        ///// <summary>
        ///// 导出村庄数据
        ///// </summary>
        ///// <param name="pageRequest">type check</param>
        ///// <returns></returns>
        //[HttpGet("villages/ExportExcel")]
        //[Produces("application/octet-stream")]
        //[ProducesResponseType(200)]
        //[ProducesResponseType(401)]
        //public IActionResult ExportExcel([FromQuery] string Text, [FromQuery] int? Pczt, [FromQuery] int? check777, string xzq, [FromQuery] int? check315 = 1)
        //{
        //    try
        //    {
        //        //var list = new List<VillageFireSafetyAttrDto>();
        //        var userNumber = this.CurrentUser();
        //        var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
        //        var sbSql = new StringBuilder();
        //        var sbSqlWhere = new StringBuilder();
        //        var sqlParams = new List<NpgsqlParameter>();
        //        string strc = " 1=1 and ";
        //        if (check777 == 777)
        //        {
        //            strc = "village_ownership in(select region_code from zm_department_xf_2023 where check777=1 and regional_level='7') and  ";
        //        }
        //        else if (check777 == 108)
        //        {
        //            strc = "village_ownership in(select region_code from zm_department_xf_2023 where check108=1 and regional_level='7') and  ";
        //        }

        //        if (check315 == 0)
        //        {
        //            sbSql.Append($@"SELECT * FROM zm_village_fire_safety_314 where " + strc + " (modified_on <= '2023-3-15' or create_on <= '2023-3-15') and (is_delete<>1 or is_delete is null) ");
        //        }
        //        else if (check315 == 1)
        //        {
        //            sbSql.Append($@"SELECT * FROM zm_village_fire_safety where  " + strc + "    (is_delete<>1 or is_delete is null) ");
        //        }
        //        var codeWhere = Utilitys.GetLikeSqlWhereVillage(user.Jurisdiction, null);
        //        if (!string.IsNullOrEmpty(codeWhere))
        //        {
        //            sbSqlWhere.Append($"{codeWhere} and");
        //        }

        //        if (!string.IsNullOrWhiteSpace(xzq))
        //        {
        //            var a = xzq.Split(",");
        //            sbSqlWhere.Append($@" (  ");
        //            for (int item = 0; item < a.Length; item++)

        //            {
        //                if (item > 0)
        //                {
        //                    if (string.IsNullOrWhiteSpace(a[item]))
        //                    {
        //                        continue;
        //                    }
        //                    sbSqlWhere.Append($@"  or ");
        //                }
        //                if (a[item].Replace("000", "").Length == 6)
        //                {
        //                    sbSqlWhere.Append($@"  county like '%{a[item]}'  ");
        //                }
        //                if (a[item].Replace("000", "").Length == 9)
        //                {
        //                    sbSqlWhere.Append($@"  country like '%{a[item]}'   ");
        //                }
        //                if (a[item].Replace("000", "").Length == 12)
        //                {
        //                    sbSqlWhere.Append($@"  village like '%{a[item]}'   ");
        //                }
        //            }
        //            sbSqlWhere.Append($@" ) and ");
        //        }

        //        if (!string.IsNullOrEmpty(Text))
        //        {
        //            sbSqlWhere.Append($@" village_name like @village_name and");
        //            sqlParams.Add(new NpgsqlParameter("village_name", $"%{Text}%"));
        //        }

        //        if (Pczt.HasValue)
        //        {
        //            sbSqlWhere.Append($@" pczt = @pczt and");
        //            sqlParams.Add(new NpgsqlParameter("pczt", Pczt.Value));
        //        }

        //        if (!string.IsNullOrEmpty(sbSqlWhere.ToString()))
        //        {
        //            sbSql.Append($@" and {sbSqlWhere} 1=1 ");
        //        }


        //        //写入缓存

        //        var list = _dbContext.ZmVillageFireSafety
        // .FromSqlRaw(sbSql.ToString(), sqlParams.ToArray());
        //        if (list == null)
        //        {
        //            return BadRequest("未找到数据");
        //        }
        //        //var fileName = Path.GetTempFileName();
        //        //var mapper = new Npoi.Mapper.Mapper();
        //        //mapper.Put(list, "任务包房屋数据", true);
        //        //mapper.Save(fileName);
        //        //return (IActionResult)Task.FromResult(File(new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)
        //        //    , "application/octet-stream"
        //        //    , $"dot_export_{DateTime.Now:yyyyMMdd_HHmmss}.xlsx"));
        //        //区名称	街镇名称	已排查房屋	有隐患房屋	发现隐患数	消除隐患数	剩余隐患数(核查)	剩余隐患数(排查)
        //        IWorkbook wb = new XSSFWorkbook();
        //        ISheet sheet = wb.CreateSheet("Sheet1");
        //        // 第一行 标题
        //        IRow row = sheet.CreateRow(0);
        //        row.CreateCell(0).SetCellValue("序号");
        //        row.CreateCell(1).SetCellValue("排查状态");
        //        row.CreateCell(2).SetCellValue("行政村归属");
        //        row.CreateCell(3).SetCellValue("行政村名称");
        //        row.CreateCell(4).SetCellValue("村域内的电动自行车数量（辆）");
        //        row.CreateCell(5).SetCellValue("生产经营租住村（居）民自建房集中连片区域（个）");
        //        row.CreateCell(6).SetCellValue("村域内村民自建出租房的建筑数（栋）");
        //        row.CreateCell(7).SetCellValue("消防车道数量");
        //        row.CreateCell(8).SetCellValue("消防水池数量");
        //        row.CreateCell(9).SetCellValue("室外消火栓数量");
        //        row.CreateCell(10).SetCellValue("消防水鹤数量");
        //        row.CreateCell(11).SetCellValue("电动自行车充电设施数量");
        //        row.CreateCell(12).SetCellValue("专职消防队数量");
        //        row.CreateCell(13).SetCellValue("专职消防队员数量");
        //        row.CreateCell(14).SetCellValue("水罐消防车数量");
        //        row.CreateCell(15).SetCellValue("微型消防站数量");
        //        row.CreateCell(16).SetCellValue("微型消防站队员数量");
        //        row.CreateCell(17).SetCellValue("微型消防站消防车数量");
        //        // 第二行 写数据
        //        int i = 1;
        //        foreach (var item in list)
        //        {
        //            row = sheet.CreateRow(i);
        //            row.CreateCell(0).SetCellValue(i);
        //            if (item.Pczt == 0)
        //            {
        //                row.CreateCell(1).SetCellValue("未排查");
        //            }
        //            else if (item.Pczt == 1)
        //            {
        //                row.CreateCell(1).SetCellValue("排查中");
        //            }
        //            else if (item.Pczt == 2)
        //            {
        //                row.CreateCell(1).SetCellValue("已排查");
        //            }

        //            row.CreateCell(2).SetCellValue(item.VillageOwnership);
        //            row.CreateCell(3).SetCellValue(item.VillageName);
        //            row.CreateCell(4).SetCellValue(item.ElectricBicycleNumber == null ? 0 : (int)item.ElectricBicycleNumber);
        //            row.CreateCell(5).SetCellValue(item.SelfBuiltHousesAreaNumber == null ? 0 : (int)item.SelfBuiltHousesAreaNumber);
        //            row.CreateCell(6).SetCellValue(item.SelfBuiltRentalHouseNumber == null ? 0 : (int)item.SelfBuiltRentalHouseNumber);
        //            row.CreateCell(7).SetCellValue(item.FireLanesNumber == null ? 0 : (int)item.FireLanesNumber);
        //            row.CreateCell(8).SetCellValue(item.FirePoolNumber == null ? 0 : (int)item.FirePoolNumber);
        //            row.CreateCell(9).SetCellValue(item.OutdoorFireHydrantNumber == null ? 0 : (int)item.OutdoorFireHydrantNumber);
        //            row.CreateCell(10).SetCellValue(item.FireWaterCraneNumber == null ? 0 : (int)item.FireWaterCraneNumber);
        //            row.CreateCell(11).SetCellValue(item.ChargingFacilitiesNumber == null ? 0 : (int)item.ChargingFacilitiesNumber);
        //            row.CreateCell(12).SetCellValue(item.FireBrigadeNumber == null ? 0 : (int)item.FireBrigadeNumber);
        //            row.CreateCell(13).SetCellValue(item.FireFighterNumber == null ? 0 : (int)item.FireFighterNumber);
        //            row.CreateCell(14).SetCellValue(item.FireTruckNumber == null ? 0 : (int)item.FireTruckNumber);
        //            row.CreateCell(15).SetCellValue(item.FireStationNumber == null ? 0 : (int)item.FireStationNumber);
        //            row.CreateCell(16).SetCellValue(item.FireStationPersonnelNumber == null ? 0 : (int)item.FireStationPersonnelNumber);
        //            row.CreateCell(17).SetCellValue(item.FireStationTruckNumber == null ? 0 : (int)item.FireStationTruckNumber);

        //            //ICell cell = row.CreateCell(10);
        //            //ICellStyle style = wb.CreateCellStyle();
        //            //IDataFormat format = wb.CreateDataFormat();
        //            //style.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
        //            //cell.CellStyle = style;
        //            //cell.SetCellValue(item.createdate);
        //            //row.CreateCell(10).SetCellValue(item.createdate);
        //            //// 日期格式的导出
        //            //ICell cell = row.CreateCell(4);
        //            //ICellStyle style = wb.CreateCellStyle();
        //            //IDataFormat format = wb.CreateDataFormat();
        //            //style.DataFormat = format.GetFormat("yyyy-MM-dd");
        //            //cell.CellStyle = style;
        //            //cell.SetCellValue(DateTime.Parse(item.InDate.ToString("yyyy-MM-dd")));

        //            //row.CreateCell(5).SetCellValue((double)item.JF);
        //            //row.CreateCell(6).SetCellValue(item.Pic);
        //            //row.CreateCell(7).SetCellValue(item.State);
        //            i++;
        //        }
        //        // 写 WorkBook信息到 内存流中
        //        byte[] buffer = null;
        //        using (MemoryStream ms = new MemoryStream())
        //        {
        //            wb.Write(ms);
        //            buffer = ms.ToArray();
        //        }
        //        return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "村庄消防安全信息排查.xlsx");
        //    }
        //    catch (Exception ex)
        //    {
        //        throw;
        //    }

        //}
        //}
        /// <summary>
        /// 导出村庄数据
        /// </summary>
        /// <param name="pageRequest">type check</param>
        /// <returns></returns>
        [HttpGet("villages/ExportExcel")]
        [Produces("application/octet-stream")]
        [ProducesResponseType(200)]
        [ProducesResponseType(401)]
        public IActionResult ExportExcel([FromQuery] string Text, [FromQuery] int? Pczt, [FromQuery] int? check777, [FromQuery] string xzq, [FromQuery] int? check315 = 1)
        {
            try
            {
                //var list = new List<VillageFireSafetyAttrDto>();
                var userNumber = this.CurrentUser();
                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
                var sbSql = new StringBuilder();
                var sbSqlWhere = new StringBuilder();
                var sqlParams = new List<NpgsqlParameter>();
                string strc = " 1=1 and ";
                if (check777 == 777)
                {
                    strc = "village_ownership in(select region_code from zm_department_xf_2023 where check777=1 and regional_level='7') and  ";
                }
                else if (check777 == 108)
                {
                    strc = "village_ownership in(select region_code from zm_department_xf_2023 where check108=1 and regional_level='7') and  ";
                }

                if (check315 == 0)
                {
                    sbSql.Append($@"SELECT * FROM zm_village_fire_safety_314 where " + strc + " (modified_on <= '2023-3-15' or create_on <= '2023-3-15') and (is_delete<>1 or is_delete is null) ");
                }
                else if (check315 == 1)
                {
                    sbSql.Append($@"SELECT (select distinct(townname) from zm_department_xf_2023 where town=country) , (select distinct(xzqdmname) from zm_department_xf_2023 where town=country) ,* FROM zm_village_fire_safety where  " + strc + "    (is_delete<>1 or is_delete is null) ");
                }
                var codeWhere = Utilitys.GetLikeSqlWhereVillage(user.Jurisdiction, null);
                if (!string.IsNullOrEmpty(codeWhere))
                {
                    sbSqlWhere.Append($"{codeWhere} and");
                }

                if (!string.IsNullOrWhiteSpace(xzq))
                {
                    var a = xzq.Split(",");
                    sbSqlWhere.Append($@" (  ");
                    for (int item = 0; item < a.Length; item++)

                    {
                        if (item > 0)
                        {
                            if (string.IsNullOrWhiteSpace(a[item]))
                            {
                                continue;
                            }
                            sbSqlWhere.Append($@"  or ");
                        }
                        if (a[item].Replace("000", "").Length == 6)
                        {
                            sbSqlWhere.Append($@"  county like '%{a[item]}'  ");
                        }
                        if (a[item].Replace("000", "").Length == 9)
                        {
                            sbSqlWhere.Append($@"  country like '%{a[item]}'   ");
                        }
                        if (a[item].Replace("000", "").Length == 12)
                        {
                            sbSqlWhere.Append($@"  village like '%{a[item]}'   ");
                        }
                    }
                    sbSqlWhere.Append($@" ) and ");
                }

                if (!string.IsNullOrEmpty(Text))
                {
                    sbSqlWhere.Append($@" village_name like @village_name and");
                    sqlParams.Add(new NpgsqlParameter("village_name", $"%{Text}%"));
                }

                if (Pczt.HasValue)
                {
                    sbSqlWhere.Append($@" pczt = @pczt and");
                    sqlParams.Add(new NpgsqlParameter("pczt", Pczt.Value));
                }

                if (!string.IsNullOrEmpty(sbSqlWhere.ToString()))
                {
                    sbSql.Append($@" and {sbSqlWhere} 1=1 ");
                }

                sbSql.Append($@" order by village ");
                //写入缓存
                var list = _dbContext.ZmVillageFireSafetyDto
         .FromSqlRaw(sbSql.ToString(), sqlParams.ToArray());
                if (list == null)
                {
                    return BadRequest("未找到数据");
                }
                //var fileName = Path.GetTempFileName();
                //var mapper = new Npoi.Mapper.Mapper();
                //mapper.Put(list, "任务包房屋数据", true);
                //mapper.Save(fileName);
                //return (IActionResult)Task.FromResult(File(new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)
                //    , "application/octet-stream"
                //    , $"dot_export_{DateTime.Now:yyyyMMdd_HHmmss}.xlsx"));
                //区名称	街镇名称	已排查房屋	有隐患房屋	发现隐患数	消除隐患数	剩余隐患数(核查)	剩余隐患数(排查)
                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet("Sheet1");
                // 第一行 标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("区县");
                row.CreateCell(1).SetCellValue("街镇");
                row.CreateCell(2).SetCellValue("村庄");
                row.CreateCell(3).SetCellValue("编码");
                row.CreateCell(4).SetCellValue("村域内的电动自行车数量(辆)");
                row.CreateCell(5).SetCellValue("村域内村民自建出租房的建筑数(栋)");
                row.CreateCell(6).SetCellValue("消防车道数量");
                row.CreateCell(7).SetCellValue("消防水池数量");
                row.CreateCell(8).SetCellValue("室外消火栓数量");
                row.CreateCell(9).SetCellValue("消防水鹤数量");
                row.CreateCell(10).SetCellValue("电动自行车充电设施数量");
                row.CreateCell(11).SetCellValue("专职消防队数量");
                row.CreateCell(12).SetCellValue("专职消防队员数量");
                row.CreateCell(13).SetCellValue("水罐消防车数量");
                row.CreateCell(14).SetCellValue("微型消防站数量");
                row.CreateCell(15).SetCellValue("微型消防站队员数量");
                row.CreateCell(16).SetCellValue("微型消防站消防车数量");

                // 第二行 写数据
                int i = 1;
                foreach (var item in list)
                {
                    row = sheet.CreateRow(i);

                    row.CreateCell(0).SetCellValue(item.XzqdmName);
                    row.CreateCell(1).SetCellValue(item.TownName);
                    row.CreateCell(2).SetCellValue(item.VillageName);
                    row.CreateCell(3).SetCellValue(item.VillageOwnership);
                    row.CreateCell(4).SetCellValue(item.ElectricBicycleNumber == null ? 0 : (int)item.ElectricBicycleNumber);
                    //  row.CreateCell(5).SetCellValue(item.SelfBuiltHousesAreaNumber == null ? 0 : (int)item.SelfBuiltHousesAreaNumber);
                    row.CreateCell(5).SetCellValue(item.SelfBuiltRentalHouseNumber == null ? 0 : (int)item.SelfBuiltRentalHouseNumber);
                    row.CreateCell(6).SetCellValue(item.FireLanesNumber == null ? 0 : (int)item.FireLanesNumber);
                    row.CreateCell(7).SetCellValue(item.FirePoolNumber == null ? 0 : (int)item.FirePoolNumber);
                    row.CreateCell(8).SetCellValue(item.OutdoorFireHydrantNumber == null ? 0 : (int)item.OutdoorFireHydrantNumber);
                    row.CreateCell(9).SetCellValue(item.FireWaterCraneNumber == null ? 0 : (int)item.FireWaterCraneNumber);
                    row.CreateCell(10).SetCellValue(item.ChargingFacilitiesNumber == null ? 0 : (int)item.ChargingFacilitiesNumber);
                    row.CreateCell(11).SetCellValue(item.FireBrigadeNumber == null ? 0 : (int)item.FireBrigadeNumber);
                    row.CreateCell(12).SetCellValue(item.FireFighterNumber == null ? 0 : (int)item.FireFighterNumber);
                    row.CreateCell(13).SetCellValue(item.FireTruckNumber == null ? 0 : (int)item.FireTruckNumber);
                    row.CreateCell(14).SetCellValue(item.FireStationNumber == null ? 0 : (int)item.FireStationNumber);
                    row.CreateCell(15).SetCellValue(item.FireStationPersonnelNumber == null ? 0 : (int)item.FireStationPersonnelNumber);
                    row.CreateCell(16).SetCellValue(item.FireStationTruckNumber == null ? 0 : (int)item.FireStationTruckNumber);

                    //ICell cell = row.CreateCell(10);
                    //ICellStyle style = wb.CreateCellStyle();
                    //IDataFormat format = wb.CreateDataFormat();
                    //style.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
                    //cell.CellStyle = style;
                    //cell.SetCellValue(item.createdate);
                    //row.CreateCell(10).SetCellValue(item.createdate);
                    //// 日期格式的导出
                    //ICell cell = row.CreateCell(4);
                    //ICellStyle style = wb.CreateCellStyle();
                    //IDataFormat format = wb.CreateDataFormat();
                    //style.DataFormat = format.GetFormat("yyyy-MM-dd");
                    //cell.CellStyle = style;
                    //cell.SetCellValue(DateTime.Parse(item.InDate.ToString("yyyy-MM-dd")));

                    //row.CreateCell(5).SetCellValue((double)item.JF);
                    //row.CreateCell(6).SetCellValue(item.Pic);
                    //row.CreateCell(7).SetCellValue(item.State);
                    i++;
                }
                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "村庄消防安全信息排查.xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }

        }


        ///// <summary>
        ///// 导出村庄数据
        ///// </summary>
        ///// <param name="pageRequest">type check</param>
        ///// <returns></returns>
        //[HttpGet("villages/ExportExcelYj")]
        //[Produces("application/octet-stream")]
        //[ProducesResponseType(200)]
        //[ProducesResponseType(401)]
        //public IActionResult ExportExcelYj([FromQuery] string Text, [FromQuery] int? Pczt, [FromQuery] int? check777,  [FromQuery] string xzq,[FromQuery] int? check315 = 1)
        //{
        //    try
        //    {
        //        //var list = new List<VillageFireSafetyAttrDto>();
        //        var userNumber = this.CurrentUser();
        //        var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
        //        var sbSql = new StringBuilder();
        //        var sbSqlWhere = new StringBuilder();
        //        var sqlParams = new List<NpgsqlParameter>();
        //        string strc = " 1=1 and ";
        //        if (check777 == 777)
        //        {
        //            strc = "village_ownership in(select region_code from zm_department_xf_2023 where check777=1 and regional_level='7') and  ";
        //        }
        //        else if (check777 == 108)
        //        {
        //            strc = "village_ownership in(select region_code from zm_department_xf_2023 where check108=1 and regional_level='7') and  ";
        //        }

        //        if (check315 == 0)
        //        {
        //            sbSql.Append($@"SELECT * FROM zm_village_fire_safety_314 where " + strc + " (modified_on <= '2023-3-15' or create_on <= '2023-3-15') and (is_delete<>1 or is_delete is null) ");
        //        }
        //        else if (check315 == 1)
        //        {
        //            sbSql.Append($@"SELECT * FROM zm_village_fire_safety_yj where  " + strc + "    (is_delete<>1 or is_delete is null) ");
        //        }
        //        var codeWhere = Utilitys.GetLikeSqlWhereVillage(user.Jurisdiction, null);
        //        if (!string.IsNullOrEmpty(codeWhere))
        //        {
        //            sbSqlWhere.Append($"{codeWhere} and");
        //        }

        //         if (!string.IsNullOrWhiteSpace(xzq))
        //        {
        //            var a = xzq.Split(",");
        //            sbSqlWhere.Append($@" (  ");
        //            for (int item = 0; item < a.Length; item++)

        //            {
        //                if (item > 0)
        //                {
        //                    if (string.IsNullOrWhiteSpace(a[item]))
        //                    {
        //                        continue;
        //                    }
        //                    sbSqlWhere.Append($@"  or ");
        //                }
        //                if (a[item].Replace("000", "").Length == 6)
        //                {
        //                    sbSqlWhere.Append($@"  county like '%{a[item]}'  ");
        //                }
        //                if (a[item].Replace("000", "").Length == 9)
        //                {
        //                    sbSqlWhere.Append($@"  country like '%{a[item]}'   ");
        //                }
        //                if (a[item].Replace("000", "").Length == 12)
        //                {
        //                    sbSqlWhere.Append($@"  village like '%{a[item]}'   ");
        //                }
        //            }
        //            sbSqlWhere.Append($@" ) and ");
        //        }

        //        if (!string.IsNullOrEmpty(Text))
        //        {
        //            sbSqlWhere.Append($@" village_name like @village_name and");
        //            sqlParams.Add(new NpgsqlParameter("village_name", $"%{Text}%"));
        //        }

        //        if (Pczt.HasValue)
        //        {
        //            sbSqlWhere.Append($@" pczt = @pczt and");
        //            sqlParams.Add(new NpgsqlParameter("pczt", Pczt.Value));
        //        }

        //        if (!string.IsNullOrEmpty(sbSqlWhere.ToString()))
        //        {
        //            sbSql.Append($@" and {sbSqlWhere} 1=1 ");
        //        }

        //        sbSqlWhere.Append($@" order by village ");
        //        //写入缓存

        //        var list = _dbContext.ZmVillageFireSafety
        // .FromSqlRaw(sbSql.ToString(), sqlParams.ToArray());
        //        if (list == null)
        //        {
        //            return BadRequest("未找到数据");
        //        }
        //        //var fileName = Path.GetTempFileName();
        //        //var mapper = new Npoi.Mapper.Mapper();
        //        //mapper.Put(list, "任务包房屋数据", true);
        //        //mapper.Save(fileName);
        //        //return (IActionResult)Task.FromResult(File(new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)
        //        //    , "application/octet-stream"
        //        //    , $"dot_export_{DateTime.Now:yyyyMMdd_HHmmss}.xlsx"));
        //        //区名称	街镇名称	已排查房屋	有隐患房屋	发现隐患数	消除隐患数	剩余隐患数(核查)	剩余隐患数(排查)
        //        IWorkbook wb = new XSSFWorkbook();
        //        ISheet sheet = wb.CreateSheet("Sheet1");
        //        // 第一行 标题
        //        IRow row = sheet.CreateRow(0);
        //        row.CreateCell(0).SetCellValue("序号");
        //        row.CreateCell(1).SetCellValue("排查状态");
        //        row.CreateCell(2).SetCellValue("行政村归属");
        //        row.CreateCell(3).SetCellValue("行政村名称");
        //        row.CreateCell(4).SetCellValue("村域内的电动自行车数量（辆）");
        //        row.CreateCell(5).SetCellValue("生产经营租住村（居）民自建房集中连片区域（个）");
        //        row.CreateCell(6).SetCellValue("村域内村民自建出租房的建筑数（栋）");
        //        row.CreateCell(7).SetCellValue("消防车道数量");
        //        row.CreateCell(8).SetCellValue("消防水池数量");
        //        row.CreateCell(9).SetCellValue("室外消火栓数量");
        //        row.CreateCell(10).SetCellValue("消防水鹤数量");
        //        row.CreateCell(11).SetCellValue("电动自行车充电设施数量");
        //        row.CreateCell(12).SetCellValue("专职消防队数量");
        //        row.CreateCell(13).SetCellValue("专职消防队员数量");
        //        row.CreateCell(14).SetCellValue("水罐消防车数量");
        //        row.CreateCell(15).SetCellValue("微型消防站数量");
        //        row.CreateCell(16).SetCellValue("微型消防站队员数量");
        //        row.CreateCell(17).SetCellValue("微型消防站消防车数量");
        //        // 第二行 写数据
        //        int i = 1;
        //        foreach (var item in list)
        //        {
        //            row = sheet.CreateRow(i);
        //            row.CreateCell(0).SetCellValue(i);
        //            if (item.Pczt == 0)
        //            {
        //                row.CreateCell(1).SetCellValue("未排查");
        //            }
        //            else if (item.Pczt == 1)
        //            {
        //                row.CreateCell(1).SetCellValue("排查中");
        //            }
        //            else if (item.Pczt == 2)
        //            {
        //                row.CreateCell(1).SetCellValue("已排查");
        //            }

        //            row.CreateCell(2).SetCellValue(item.VillageOwnership);
        //            row.CreateCell(3).SetCellValue(item.VillageName);
        //            row.CreateCell(4).SetCellValue(item.ElectricBicycleNumber == null ? 0 : (int)item.ElectricBicycleNumber);
        //            row.CreateCell(5).SetCellValue(item.SelfBuiltHousesAreaNumber == null ? 0 : (int)item.SelfBuiltHousesAreaNumber);
        //            row.CreateCell(6).SetCellValue(item.SelfBuiltRentalHouseNumber == null ? 0 : (int)item.SelfBuiltRentalHouseNumber);
        //            row.CreateCell(7).SetCellValue(item.FireLanesNumber == null ? 0 : (int)item.FireLanesNumber);
        //            row.CreateCell(8).SetCellValue(item.FirePoolNumber == null ? 0 : (int)item.FirePoolNumber);
        //            row.CreateCell(9).SetCellValue(item.OutdoorFireHydrantNumber == null ? 0 : (int)item.OutdoorFireHydrantNumber);
        //            row.CreateCell(10).SetCellValue(item.FireWaterCraneNumber == null ? 0 : (int)item.FireWaterCraneNumber);
        //            row.CreateCell(11).SetCellValue(item.ChargingFacilitiesNumber == null ? 0 : (int)item.ChargingFacilitiesNumber);
        //            row.CreateCell(12).SetCellValue(item.FireBrigadeNumber == null ? 0 : (int)item.FireBrigadeNumber);
        //            row.CreateCell(13).SetCellValue(item.FireFighterNumber == null ? 0 : (int)item.FireFighterNumber);
        //            row.CreateCell(14).SetCellValue(item.FireTruckNumber == null ? 0 : (int)item.FireTruckNumber);
        //            row.CreateCell(15).SetCellValue(item.FireStationNumber == null ? 0 : (int)item.FireStationNumber);
        //            row.CreateCell(16).SetCellValue(item.FireStationPersonnelNumber == null ? 0 : (int)item.FireStationPersonnelNumber);
        //            row.CreateCell(17).SetCellValue(item.FireStationTruckNumber == null ? 0 : (int)item.FireStationTruckNumber);

        //            //ICell cell = row.CreateCell(10);
        //            //ICellStyle style = wb.CreateCellStyle();
        //            //IDataFormat format = wb.CreateDataFormat();
        //            //style.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
        //            //cell.CellStyle = style;
        //            //cell.SetCellValue(item.createdate);
        //            //row.CreateCell(10).SetCellValue(item.createdate);
        //            //// 日期格式的导出
        //            //ICell cell = row.CreateCell(4);
        //            //ICellStyle style = wb.CreateCellStyle();
        //            //IDataFormat format = wb.CreateDataFormat();
        //            //style.DataFormat = format.GetFormat("yyyy-MM-dd");
        //            //cell.CellStyle = style;
        //            //cell.SetCellValue(DateTime.Parse(item.InDate.ToString("yyyy-MM-dd")));

        //            //row.CreateCell(5).SetCellValue((double)item.JF);
        //            //row.CreateCell(6).SetCellValue(item.Pic);
        //            //row.CreateCell(7).SetCellValue(item.State);
        //            i++;
        //        }
        //        // 写 WorkBook信息到 内存流中
        //        byte[] buffer = null;
        //        using (MemoryStream ms = new MemoryStream())
        //        {
        //            wb.Write(ms);
        //            buffer = ms.ToArray();
        //        }
        //        return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "村庄消防安全信息排查.xlsx");
        //    }
        //    catch (Exception ex)
        //    {
        //        throw;
        //    }

        //}
        /// <summary>
        /// 导出村庄数据
        /// </summary>
        /// <param name="pageRequest">type check</param>
        /// <returns></returns>
        [HttpGet("villages/ExportExcelYj")]
        [Produces("application/octet-stream")]
        [ProducesResponseType(200)]
        [ProducesResponseType(401)]
        public IActionResult ExportExcelYj([FromQuery] string Text, [FromQuery] int? Pczt, [FromQuery] int? check777, [FromQuery] string? xzq, [FromQuery] int? check315 = 1)
        {
            try
            {
                //var list = new List<VillageFireSafetyAttrDto>();
                var userNumber = this.CurrentUser();
                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
                var sbSql = new StringBuilder();
                var sbSqlWhere = new StringBuilder();
                var sqlParams = new List<NpgsqlParameter>();
                string strc = " 1=1 and ";
                if (check777 == 777)
                {
                    strc = "village_ownership in(select region_code from zm_department_xf_2023 where check777=1 and regional_level='7') and  ";
                }
                else if (check777 == 108)
                {
                    strc = "village_ownership in(select region_code from zm_department_xf_2023 where check108=1 and regional_level='7') and  ";
                }

                if (check315 == 0)
                {
                    sbSql.Append($@"SELECT * FROM zm_village_fire_safety_314 where " + strc + " (modified_on <= '2023-3-15' or create_on <= '2023-3-15') and (is_delete<>1 or is_delete is null) ");
                }
                else if (check315 == 1)
                {
                    sbSql.Append($@"SELECT (select distinct(townname) from zm_department_xf_2023 where town=country) , (select distinct(xzqdmname) from zm_department_xf_2023 where town=country) ,* FROM zm_village_fire_safety_yj where  " + strc + "    (is_delete<>1 or is_delete is null) ");
                }
                var codeWhere = Utilitys.GetLikeSqlWhereVillage(user.Jurisdiction, null);
                if (!string.IsNullOrEmpty(codeWhere))
                {
                    sbSqlWhere.Append($"{codeWhere} and");
                }

                if (!string.IsNullOrWhiteSpace(xzq))
                {
                    var a = xzq.Split(",");
                    sbSqlWhere.Append($@" (  ");
                    for (int item = 0; item < a.Length; item++)

                    {
                        if (item > 0)
                        {
                            if (string.IsNullOrWhiteSpace(a[item]))
                            {
                                continue;
                            }
                            sbSqlWhere.Append($@"  or ");
                        }
                        if (a[item].Replace("000", "").Length == 6)
                        {
                            sbSqlWhere.Append($@"  county like '%{a[item]}'  ");
                        }
                        if (a[item].Replace("000", "").Length == 9)
                        {
                            sbSqlWhere.Append($@"  country like '%{a[item]}'   ");
                        }
                        if (a[item].Replace("000", "").Length == 12)
                        {
                            sbSqlWhere.Append($@"  village like '%{a[item]}'   ");
                        }
                    }
                    sbSqlWhere.Append($@" ) and ");
                }

                if (!string.IsNullOrEmpty(Text))
                {
                    sbSqlWhere.Append($@" village_name like @village_name and");
                    sqlParams.Add(new NpgsqlParameter("village_name", $"%{Text}%"));
                }

                if (Pczt.HasValue)
                {
                    sbSqlWhere.Append($@" pczt = @pczt and");
                    sqlParams.Add(new NpgsqlParameter("pczt", Pczt.Value));
                }

                if (!string.IsNullOrEmpty(sbSqlWhere.ToString()))
                {
                    sbSql.Append($@" and {sbSqlWhere} 1=1 ");
                }

                sbSql.Append($@" order by village ");
                //写入缓存
                var list = _dbContext.ZmVillageFireSafetyDto
         .FromSqlRaw(sbSql.ToString(), sqlParams.ToArray());
                if (list == null)
                {
                    return BadRequest("未找到数据");
                }
                //var fileName = Path.GetTempFileName();
                //var mapper = new Npoi.Mapper.Mapper();
                //mapper.Put(list, "任务包房屋数据", true);
                //mapper.Save(fileName);
                //return (IActionResult)Task.FromResult(File(new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)
                //    , "application/octet-stream"
                //    , $"dot_export_{DateTime.Now:yyyyMMdd_HHmmss}.xlsx"));
                //区名称	街镇名称	已排查房屋	有隐患房屋	发现隐患数	消除隐患数	剩余隐患数(核查)	剩余隐患数(排查)
                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet("Sheet1");
                // 第一行 标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("区县");
                row.CreateCell(1).SetCellValue("街镇");
                row.CreateCell(2).SetCellValue("村庄");
                row.CreateCell(3).SetCellValue("编码");
                row.CreateCell(4).SetCellValue("村域内的电动自行车数量(辆)");
                row.CreateCell(5).SetCellValue("村域内村民自建出租房的建筑数(栋)");
                row.CreateCell(6).SetCellValue("消防车道数量");
                row.CreateCell(7).SetCellValue("消防水池数量");
                row.CreateCell(8).SetCellValue("室外消火栓数量");
                row.CreateCell(9).SetCellValue("消防水鹤数量");
                row.CreateCell(10).SetCellValue("电动自行车充电设施数量");
                row.CreateCell(11).SetCellValue("专职消防队数量");
                row.CreateCell(12).SetCellValue("专职消防队员数量");
                row.CreateCell(13).SetCellValue("水罐消防车数量");
                row.CreateCell(14).SetCellValue("微型消防站数量");
                row.CreateCell(15).SetCellValue("微型消防站队员数量");
                row.CreateCell(16).SetCellValue("微型消防站消防车数量");
                
                // 第二行 写数据
                int i = 1;
                foreach (var item in list)
                {
                    row = sheet.CreateRow(i);

                    row.CreateCell(0).SetCellValue(item.XzqdmName);
                    row.CreateCell(1).SetCellValue(item.TownName);
                     row.CreateCell(2).SetCellValue(item.VillageName);
                    row.CreateCell(3).SetCellValue(item.VillageOwnership);  
                    row.CreateCell(4).SetCellValue(item.ElectricBicycleNumber == null ? 0 : (int)item.ElectricBicycleNumber);
                    //  row.CreateCell(5).SetCellValue(item.SelfBuiltHousesAreaNumber == null ? 0 : (int)item.SelfBuiltHousesAreaNumber);
                    row.CreateCell(5).SetCellValue(item.SelfBuiltRentalHouseNumber == null ? 0 : (int)item.SelfBuiltRentalHouseNumber);
                    row.CreateCell(6).SetCellValue(item.FireLanesNumber == null ? 0 : (int)item.FireLanesNumber);
                    row.CreateCell(7).SetCellValue(item.FirePoolNumber == null ? 0 : (int)item.FirePoolNumber);
                    row.CreateCell(8).SetCellValue(item.OutdoorFireHydrantNumber == null ? 0 : (int)item.OutdoorFireHydrantNumber);
                    row.CreateCell(9).SetCellValue(item.FireWaterCraneNumber == null ? 0 : (int)item.FireWaterCraneNumber);
                    row.CreateCell(10).SetCellValue(item.ChargingFacilitiesNumber == null ? 0 : (int)item.ChargingFacilitiesNumber);
                    row.CreateCell(11).SetCellValue(item.FireBrigadeNumber == null ? 0 : (int)item.FireBrigadeNumber);
                    row.CreateCell(12).SetCellValue(item.FireFighterNumber == null ? 0 : (int)item.FireFighterNumber);
                    row.CreateCell(13).SetCellValue(item.FireTruckNumber == null ? 0 : (int)item.FireTruckNumber);
                    row.CreateCell(14).SetCellValue(item.FireStationNumber == null ? 0 : (int)item.FireStationNumber);
                    row.CreateCell(15).SetCellValue(item.FireStationPersonnelNumber == null ? 0 : (int)item.FireStationPersonnelNumber);
                    row.CreateCell(16).SetCellValue(item.FireStationTruckNumber == null ? 0 : (int)item.FireStationTruckNumber);

                    //ICell cell = row.CreateCell(10);
                    //ICellStyle style = wb.CreateCellStyle();
                    //IDataFormat format = wb.CreateDataFormat();
                    //style.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
                    //cell.CellStyle = style;
                    //cell.SetCellValue(item.createdate);
                    //row.CreateCell(10).SetCellValue(item.createdate);
                    //// 日期格式的导出
                    //ICell cell = row.CreateCell(4);
                    //ICellStyle style = wb.CreateCellStyle();
                    //IDataFormat format = wb.CreateDataFormat();
                    //style.DataFormat = format.GetFormat("yyyy-MM-dd");
                    //cell.CellStyle = style;
                    //cell.SetCellValue(DateTime.Parse(item.InDate.ToString("yyyy-MM-dd")));

                    //row.CreateCell(5).SetCellValue((double)item.JF);
                    //row.CreateCell(6).SetCellValue(item.Pic);
                    //row.CreateCell(7).SetCellValue(item.State);
                    i++;
                }
                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "村庄消防安全信息排查.xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }

        }
        /// <summary>
        /// 导出村庄数据all
        /// </summary>
        /// <param name="id">ID</param>
        /// <param name="exportOption"></param>
        /// <returns></returns>
        [HttpGet("villages/ExportExcelall")]
        [Produces("application/octet-stream")]
        [ProducesResponseType(200)]
        [ProducesResponseType(401)]
        public IActionResult ExportExcelall([FromBody] PageVillagePcztRequestInput pageRequest)
        {
            try
            {
                //var list = new List<VillageFireSafetyAttrDto>();
                var userNumber = this.CurrentUser();
                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
                var sbSql = new StringBuilder();
                var sbSqlWhere = new StringBuilder();
                var sqlParams = new List<NpgsqlParameter>();
                string strc = " 1=1 and ";
                sbSql.Append($@"SELECT * FROM zm_village_fire_safety where (is_delete<>1 or is_delete is null) ");
                var codeWhere = Utilitys.GetLikeSqlWhereVillage(user.Jurisdiction, null);
                if (!string.IsNullOrEmpty(codeWhere))
                {
                    sbSqlWhere.Append($"{codeWhere} and");
                }

                if (pageRequest.Districts != null)
                {
                    var districtCodeWhere = Utilitys.GetLikeSqlWhereVillage(JsonConvert.SerializeObject(pageRequest.Districts), null);
                    if (!string.IsNullOrEmpty(districtCodeWhere))
                    {
                        sbSqlWhere.Append($"{districtCodeWhere} and");
                    }
                }

                if (!string.IsNullOrEmpty(pageRequest.Text))
                {
                    sbSqlWhere.Append($@" village_name like @village_name and");
                    sqlParams.Add(new NpgsqlParameter("village_name", $"%{pageRequest.Text}%"));
                }

                if (pageRequest.Pczt.HasValue)
                {
                    sbSqlWhere.Append($@" pczt = @pczt and");
                    sqlParams.Add(new NpgsqlParameter("pczt", pageRequest.Pczt.Value));
                }

                if (!string.IsNullOrEmpty(sbSqlWhere.ToString()))
                {
                    sbSql.Append($@" and {sbSqlWhere} 1=1 ");
                }


                //写入缓存

                var list = _dbContext.ZmVillageFireSafety
         .FromSqlRaw(sbSql.ToString(), sqlParams.ToArray());
                if (list == null)
                {
                    return BadRequest("未找到数据");
                }
                //var fileName = Path.GetTempFileName();
                //var mapper = new Npoi.Mapper.Mapper();
                //mapper.Put(list, "任务包房屋数据", true);
                //mapper.Save(fileName);
                //return (IActionResult)Task.FromResult(File(new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)
                //    , "application/octet-stream"
                //    , $"dot_export_{DateTime.Now:yyyyMMdd_HHmmss}.xlsx"));
                //区名称	街镇名称	已排查房屋	有隐患房屋	发现隐患数	消除隐患数	剩余隐患数(核查)	剩余隐患数(排查)
                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet("Sheet1");
                // 第一行 标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("排查状态");
                row.CreateCell(2).SetCellValue("行政村归属");
                row.CreateCell(3).SetCellValue("行政村名称");
                row.CreateCell(4).SetCellValue("村域内的电动自行车数量（辆）");
                row.CreateCell(5).SetCellValue("生产经营租住村（居）民自建房集中连片区域（个）");
                row.CreateCell(6).SetCellValue("村域内村民自建出租房的建筑数（栋）");
                row.CreateCell(7).SetCellValue("消防车道数量");
                row.CreateCell(8).SetCellValue("消防水池数量");
                row.CreateCell(9).SetCellValue("室外消火栓数量");
                row.CreateCell(10).SetCellValue("消防水鹤数量");
                row.CreateCell(11).SetCellValue("电动自行车充电设施数量");
                row.CreateCell(12).SetCellValue("专职消防队数量");
                row.CreateCell(13).SetCellValue("专职消防队员数量");
                row.CreateCell(14).SetCellValue("水罐消防车数量");
                row.CreateCell(15).SetCellValue("微型消防站数量");
                row.CreateCell(16).SetCellValue("微型消防站队员数量");
                row.CreateCell(17).SetCellValue("微型消防站消防车数量");
                // 第二行 写数据
                int i = 1;
                foreach (var item in list)
                {
                    row = sheet.CreateRow(i);
                    row.CreateCell(0).SetCellValue(i);
                    if (item.Pczt == 0)
                    {
                        row.CreateCell(1).SetCellValue("未排查");
                    }
                    else if (item.Pczt == 1)
                    {
                        row.CreateCell(1).SetCellValue("排查中");
                    }
                    else if (item.Pczt == 2)
                    {
                        row.CreateCell(1).SetCellValue("已排查");
                    }
                    row.CreateCell(2).SetCellValue(item.VillageOwnership);
                    row.CreateCell(3).SetCellValue(item.VillageName);
                    row.CreateCell(4).SetCellValue(item.ElectricBicycleNumber.HasValue ? (double)item.ElectricBicycleNumber : 0);
                    row.CreateCell(5).SetCellValue(item.SelfBuiltHousesAreaNumber.HasValue ? (double)item.SelfBuiltHousesAreaNumber : 0);
                    row.CreateCell(6).SetCellValue(item.SelfBuiltRentalHouseNumber.HasValue ? (double)item.SelfBuiltRentalHouseNumber : 0);
                    row.CreateCell(7).SetCellValue(item.FireLanesNumber.HasValue ? (double)item.FireLanesNumber : 0);
                    row.CreateCell(8).SetCellValue(item.FirePoolNumber.HasValue ? (double)item.FirePoolNumber : 0);
                    row.CreateCell(9).SetCellValue(item.OutdoorFireHydrantNumber.HasValue ? (double)item.OutdoorFireHydrantNumber : 0);
                    row.CreateCell(10).SetCellValue(item.FireWaterCraneNumber.HasValue ? (double)item.FireWaterCraneNumber : 0);
                    row.CreateCell(11).SetCellValue(item.ChargingFacilitiesNumber.HasValue ? (double)item.ChargingFacilitiesNumber : 0);
                    row.CreateCell(12).SetCellValue(item.FireBrigadeNumber.HasValue ? (double)item.FireBrigadeNumber : 0);
                    row.CreateCell(13).SetCellValue(item.FireFighterNumber.HasValue ? (double)item.FireFighterNumber : 0);
                    row.CreateCell(14).SetCellValue(item.FireTruckNumber.HasValue ? (double)item.FireTruckNumber : 0);
                    row.CreateCell(15).SetCellValue(item.FireStationNumber.HasValue ? (double)item.FireStationNumber : 0);
                    row.CreateCell(16).SetCellValue(item.FireStationPersonnelNumber.HasValue ? (double)item.FireStationPersonnelNumber : 0);
                    row.CreateCell(17).SetCellValue(item.FireStationTruckNumber.HasValue ? (double)item.FireStationTruckNumber : 0);


                    //ICell cell = row.CreateCell(10);
                    //ICellStyle style = wb.CreateCellStyle();
                    //IDataFormat format = wb.CreateDataFormat();
                    //style.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
                    //cell.CellStyle = style;
                    //cell.SetCellValue(item.createdate);
                    //row.CreateCell(10).SetCellValue(item.createdate);
                    //// 日期格式的导出
                    //ICell cell = row.CreateCell(4);
                    //ICellStyle style = wb.CreateCellStyle();
                    //IDataFormat format = wb.CreateDataFormat();
                    //style.DataFormat = format.GetFormat("yyyy-MM-dd");
                    //cell.CellStyle = style;
                    //cell.SetCellValue(DateTime.Parse(item.InDate.ToString("yyyy-MM-dd")));

                    //row.CreateCell(5).SetCellValue((double)item.JF);
                    //row.CreateCell(6).SetCellValue(item.Pic);
                    //row.CreateCell(7).SetCellValue(item.State);
                    i++;
                }
                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "村庄消防安全信息排查.xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }

        }
        /// <summary>
        /// 获取村庄几何
        /// </summary>
        /// <param name="rect"></param>
        /// <returns></returns>
        [HttpGet("villages/search-geom")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<List<VillageFireSafetyGeomDto>>), 200)]
        public RespRet<List<VillageFireSafetyGeomDto>> SearchGeomList([FromQuery] ZmRect rect)
        {
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<List<VillageFireSafetyGeomDto>>(false, "user not logined");
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<List<VillageFireSafetyGeomDto>>(false, "用户名不存在");
            }

            var pts = new List<Coordinate>();
            pts.Add(new Coordinate(rect.XMin, rect.YMax));
            pts.Add(new Coordinate(rect.XMax, rect.YMax));
            pts.Add(new Coordinate(rect.XMax, rect.YMin));
            pts.Add(new Coordinate(rect.XMin, rect.YMin));
            pts.Add(new Coordinate(rect.XMin, rect.YMax));
            var line = new LinearRing(pts.ToArray());
            var polygon = new Polygon(line);
            polygon.SRID = 4326;

            List<VillageFireSafetyGeomDto> datas = null;
            var roleType = (PermissionRoleType)user.RoleType;
            if (roleType == PermissionRoleType.Admin)
            {
                datas = (from vi in _dbContext.ZmVillageFireSafety
                         where vi.IsDelete != 1
                         where vi.Geom.Within(polygon) || vi.Geom.Intersects(polygon)
                         select _mapper.Map<VillageFireSafetyGeomDto>(vi)).ToList();
            }
            else
            {
                if (user.Jurisdiction != null)
                {
                    var sbSql = new StringBuilder();
                    sbSql.Append($@"SELECT * FROM zm_village_fire_safety 
where (is_delete<>1 or is_delete is null) 
and geom && 
ST_SETSRID(ST_MakeBox2D(ST_MakePoint({rect.XMin}, {rect.YMin}),ST_MakePoint({rect.XMax}, {rect.YMax})),4326)");

                    var codeWhere = Utilitys.GetLikeSqlWhereVillage(user.Jurisdiction, null);
                    if (!string.IsNullOrEmpty(codeWhere))
                    {
                        sbSql.Append($" and {codeWhere}");
                    }

                    datas = _dbContext.ZmVillageFireSafety.FromSqlRaw(sbSql.ToString())
                        .Select(_ => _mapper.Map<VillageFireSafetyGeomDto>(_)).ToList();
                }
            }

            return new RespRet<List<VillageFireSafetyGeomDto>>(true, "Ok", datas);
        }

        /// <summary>
        /// 获取村庄信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [HttpGet("villages/{id}/info")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<VillageFireSafetyDto>), 200)]
        public RespRet<VillageFireSafetyDto> GetById([FromRoute] string id)
        {
            if (string.IsNullOrEmpty(id))
            {
                return new RespRet<VillageFireSafetyDto>(false, "id can not be null");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<VillageFireSafetyDto>(false, "user not logined");
            }

            var data = _villageFireSafetyRepository.FirstOrDefault(_ => _.Id == id && _.IsDelete != 1);
            if (null == data)
            {
                return new RespRet<VillageFireSafetyDto>(false, "村庄不存在");
            }

            return new RespRet<VillageFireSafetyDto>(true, "Ok", _mapper.Map<VillageFireSafetyDto>(data));
        }

        /// <summary>
        /// 新建村庄
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        [HttpPost("villages/create")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<string>), 200)]
        public RespRet<string> Create([FromBody] VillageCreateInput request)
        {
            if (!ModelState.IsValid)
            {
                return new RespRet<string>(false, "Invalid Request");
            }

            if (string.IsNullOrEmpty(request.VillageName))
            {
                return new RespRet<string>(false, "名称必须输入");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<string>(false, "user not logined");
            }

            Geometry geometry = null;
            if (!string.IsNullOrEmpty(request.Geometry))
            {
                try
                {
                    var reader = new GeoJsonReader();
                    geometry = reader.Read<Geometry>(request.Geometry);
                    if (null == geometry)
                    {
                        return new RespRet<string>(false, "geometry is null");
                    }
                }
                catch (Exception e1)
                {
                    return new RespRet<string>(false, $"反序列化 Geometry失败:{e1.Message}");
                }
            }
            var ishavevillage = _villageFireSafetyRepository.Query(c => c.IsDelete == null && c.Village == request.Village).FirstOrDefault();
            if (ishavevillage != null)
            {
                return new RespRet<string>(false, "已提交过该村庄");
            }
            var id = SequentialGuid.SequentialSqlGuidGenerator.Instance.NewGuid().ToString("N");
            var ipAddress = this.IpAddress();
            var time = DateTime.Now;

            var dmData = new ZmVillageFireSafety
            {
                Id = id,
                VillageOwnership = request.VillageOwnership,
                VillageName = request.VillageName,
                FireLanesNumber = request.FireLanesNumber,
                FirePoolNumber = request.FirePoolNumber,
                ChargingFacilitiesNumber = request.ChargingFacilitiesNumber,
                OutdoorFireHydrantNumber = request.OutdoorFireHydrantNumber,
                FireWaterCraneNumber = request.FireWaterCraneNumber,
                FireBrigadeNumber = request.FireBrigadeNumber,
                FireFighterNumber = request.FireFighterNumber,
                FireTruckNumber = request.FireTruckNumber,
                FireStationNumber = request.FireStationNumber,
                FireStationPersonnelNumber = request.FireStationPersonnelNumber,
                FireStationTruckNumber = request.FireStationTruckNumber,
                ElectricBicycleNumber = request.ElectricBicycleNumber,
                SelfBuiltHousesAreaNumber = request.SelfBuiltHousesAreaNumber,
                SelfBuiltRentalHouseNumber = request.SelfBuiltRentalHouseNumber,
                Province = request.Province,
                City = request.City,
                County = request.County,
                Country = request.Country,
                Village = request.Village,
                Geom = geometry,
                Pczt = 1,
                FireLayoutPhoto = request.FireLayoutPhotos != null
                ? JsonConvert.SerializeObject(request.FireLayoutPhotos) : null,
                CreateBy = userNumber,
                CreateOn = time,
                CreateIp = ipAddress,
                ModifiedBy = userNumber,
                ModifiedOn = time,
                ModifiedIp = ipAddress
            };

            _villageFireSafetyRepository.Insert(dmData);
            _villageFireSafetyRepository.SaveChanges();
            return new RespRet<string>(true, "Ok", id);
        }



        /// <summary>
        /// 新建村庄应建设
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        [HttpPost("villages/createyj")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<string>), 200)]
        public RespRet<string> Createyj([FromBody] VillageCreateInput request)
        {
            if (!ModelState.IsValid)
            {
                return new RespRet<string>(false, "Invalid Request");
            }

            if (string.IsNullOrEmpty(request.VillageName))
            {
                return new RespRet<string>(false, "行政村名称必须输入");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<string>(false, "user not logined");
            }

            Geometry geometry = null;
            if (!string.IsNullOrEmpty(request.Geometry))
            {
                try
                {
                    var reader = new GeoJsonReader();
                    geometry = reader.Read<Geometry>(request.Geometry);
                    if (null == geometry)
                    {
                        return new RespRet<string>(false, "geometry is null");
                    }
                }
                catch (Exception e1)
                {
                    return new RespRet<string>(false, $"反序列化 Geometry失败:{e1.Message}");
                }
            }
            var ishavevillage = _villageFireSafetyRepository.Query(c => c.IsDelete == null && c.Village == request.Village).FirstOrDefault();
            if (ishavevillage != null)
            {
                return new RespRet<string>(false, "已提交过该村庄");
            }
            var id = SequentialGuid.SequentialSqlGuidGenerator.Instance.NewGuid().ToString("N");
            var ipAddress = this.IpAddress();
            var time = DateTime.Now;

            var dmData = new ZmVillageFireSafetyYJ
            {
                Id = id,
                VillageOwnership = request.VillageOwnership,
                VillageName = request.VillageName,
                FireLanesNumber = request.FireLanesNumber,
                FirePoolNumber = request.FirePoolNumber,
                ChargingFacilitiesNumber = request.ChargingFacilitiesNumber,
                OutdoorFireHydrantNumber = request.OutdoorFireHydrantNumber,
                FireWaterCraneNumber = request.FireWaterCraneNumber,
                FireBrigadeNumber = request.FireBrigadeNumber,
                FireFighterNumber = request.FireFighterNumber,
                FireTruckNumber = request.FireTruckNumber,
                FireStationNumber = request.FireStationNumber,
                FireStationPersonnelNumber = request.FireStationPersonnelNumber,
                FireStationTruckNumber = request.FireStationTruckNumber,
                ElectricBicycleNumber = request.ElectricBicycleNumber,
                SelfBuiltHousesAreaNumber = request.SelfBuiltHousesAreaNumber,
                SelfBuiltRentalHouseNumber = request.SelfBuiltRentalHouseNumber,
                Province = request.Province,
                City = request.City,
                County = request.County,
                Country = request.Country,
                Village = request.Village,
                Geom = geometry,
                Pczt = 1,
                FireLayoutPhoto = request.FireLayoutPhotos != null
                ? JsonConvert.SerializeObject(request.FireLayoutPhotos) : null,
                CreateBy = userNumber,
                CreateOn = time,
                CreateIp = ipAddress,
                ModifiedBy = userNumber,
                ModifiedOn = time,
                ModifiedIp = ipAddress
            };

            _dbContext.ZmVillageFireSafetyYJ.Add(dmData);
            _dbContext.SaveChanges();
            return new RespRet<string>(true, "Ok", id);
        }
        /// <summary>
        /// 编辑村庄信息
        /// </summary>
        /// <param name="id"></param>
        /// <param name="request"></param>
        /// <returns></returns>
        [HttpPost("villages/{id}/edit")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet), 200)]
        public RespRet Edit([FromRoute] string id, [FromBody] VillageEditInput request)
        {
            if (string.IsNullOrEmpty(id))
            {
                return new RespRet(false, "id can not be null");
            }

            if (string.IsNullOrEmpty(request.VillageName))
            {
                return new RespRet<string>(false, "名称必须输入");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet(false, "user not logined");
            }

            var data = _villageFireSafetyRepository.FirstOrDefault(_ => _.Id == id && _.IsDelete != 1);
            if (null == data)
            {
                return new RespRet(false, "村庄不存在");
            }

            var ipAddress = this.IpAddress();
            var time = DateTime.Now;
            data.VillageOwnership = request.VillageOwnership;
            data.VillageName = request.VillageName;
            data.FireLanesNumber = request.FireLanesNumber;
            data.FirePoolNumber = request.FirePoolNumber;
            data.ChargingFacilitiesNumber = request.ChargingFacilitiesNumber;
            data.OutdoorFireHydrantNumber = request.OutdoorFireHydrantNumber;
            data.FireWaterCraneNumber = request.FireWaterCraneNumber;
            data.FireBrigadeNumber = request.FireBrigadeNumber;
            data.FireFighterNumber = request.FireFighterNumber;
            data.FireTruckNumber = request.FireTruckNumber;
            data.FireStationNumber = request.FireStationNumber;
            data.FireStationPersonnelNumber = request.FireStationPersonnelNumber;
            data.FireStationTruckNumber = request.FireStationTruckNumber;
            data.ElectricBicycleNumber = request.ElectricBicycleNumber;
            data.SelfBuiltHousesAreaNumber = request.SelfBuiltHousesAreaNumber;
            data.SelfBuiltRentalHouseNumber = request.SelfBuiltRentalHouseNumber;
            data.Province = request.Province;
            data.City = request.City;
            data.County = request.County;
            data.Country = request.Country;
            data.Village = request.Village;
            data.Pczt = 1;
            data.FireLayoutPhoto = request.FireLayoutPhotos != null
            ? JsonConvert.SerializeObject(request.FireLayoutPhotos) : null;
            data.ModifiedBy = userNumber;
            data.ModifiedOn = time;
            data.ModifiedIp = ipAddress;
            _villageFireSafetyRepository.Update(data);
            _villageFireSafetyRepository.SaveChanges();

            return new RespRet(true, "Ok");
        }


        /// <summary>
        /// 编辑村庄信息应建设
        /// </summary>
        /// <param name="id"></param>
        /// <param name="request"></param>
        /// <returns></returns>
        [HttpPost("villages/{id}/edityj")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet), 200)]
        public RespRet Edityj([FromRoute] string id, [FromBody] VillageEditInput request)
        {
            if (string.IsNullOrEmpty(id))
            {
                return new RespRet(false, "id can not be null");
            }

            if (string.IsNullOrEmpty(request.VillageName))
            {
                return new RespRet<string>(false, "行政村名称必须输入");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet(false, "user not logined");
            }

            var data = _dbContext.ZmVillageFireSafetyYJ.FirstOrDefault(_ => _.Id == id && _.IsDelete != 1);
            if (null == data)
            {
                return new RespRet(false, "村庄不存在");
            }

            var ipAddress = this.IpAddress();
            var time = DateTime.Now;
            data.VillageOwnership = request.VillageOwnership;
            data.VillageName = request.VillageName;
            data.FireLanesNumber = request.FireLanesNumber;
            data.FirePoolNumber = request.FirePoolNumber;
            data.ChargingFacilitiesNumber = request.ChargingFacilitiesNumber;
            data.OutdoorFireHydrantNumber = request.OutdoorFireHydrantNumber;
            data.FireWaterCraneNumber = request.FireWaterCraneNumber;
            data.FireBrigadeNumber = request.FireBrigadeNumber;
            data.FireFighterNumber = request.FireFighterNumber;
            data.FireTruckNumber = request.FireTruckNumber;
            data.FireStationNumber = request.FireStationNumber;
            data.FireStationPersonnelNumber = request.FireStationPersonnelNumber;
            data.FireStationTruckNumber = request.FireStationTruckNumber;
            data.ElectricBicycleNumber = request.ElectricBicycleNumber;
            data.SelfBuiltHousesAreaNumber = request.SelfBuiltHousesAreaNumber;
            data.SelfBuiltRentalHouseNumber = request.SelfBuiltRentalHouseNumber;
            data.Province = request.Province;
            data.City = request.City;
            data.County = request.County;
            data.Country = request.Country;
            data.Village = request.Village;
            data.Pczt = 1;
            data.FireLayoutPhoto = request.FireLayoutPhotos != null
            ? JsonConvert.SerializeObject(request.FireLayoutPhotos) : null;
            data.ModifiedBy = userNumber;
            data.ModifiedOn = time;
            data.ModifiedIp = ipAddress;
            _dbContext.ZmVillageFireSafetyYJ.Update(data);
            _dbContext.SaveChanges();

            return new RespRet(true, "Ok");
        }

        /// <summary>
        /// 删除村庄
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [HttpPost("villages/{id}/delete")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet), 200)]
        public RespRet Delete([FromRoute] string id)
        {
            if (string.IsNullOrEmpty(id))
            {
                return new RespRet(false, "id can not be null");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet(false, "user not logined");
            }

            var data = _villageFireSafetyRepository.FirstOrDefault(_ => _.Id == id && _.IsDelete != 1);
            if (null == data)
            {
                return new RespRet(false, "村庄不存在");
            }

            _villageFireSafetyRepository.Delete(data);
            _villageFireSafetyRepository.SaveChanges();

            return new RespRet(true, "Ok");
        }

        /// <summary>
        /// 删除村庄
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [HttpPost("villages/{id}/deleteyj")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet), 200)]
        public RespRet Deleteyj([FromRoute] string id)
        {
            if (string.IsNullOrEmpty(id))
            {
                return new RespRet(false, "id can not be null");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet(false, "user not logined");
            }

            var data = _dbContext.ZmVillageFireSafetyYJ.FirstOrDefault(_ => _.Id == id && _.IsDelete != 1);
            if (null == data)
            {
                return new RespRet(false, "村庄不存在");
            }

            _dbContext.ZmVillageFireSafetyYJ.Remove(data);
            _dbContext.SaveChanges();

            return new RespRet(true, "Ok");
        }


        #endregion

        private byte[] GetDynamicVectorTile(int z, int x, int y)
        {
            var sql = $"SELECT " +
                $"ST_AsMVT(P, 'ffpolygon', 4096, 'geom') AS tile " +
                $"FROM " +
                $"(SELECT " +
                $"ST_AsMVTGeom( ST_Transform(ST_SetSRID(geom, 4326), 3857), ST_TileEnvelope( {z}, {x}, {y}),4096,256,FALSE) AS geom, " +
                $"id, village_name " +
                $"FROM " +
                $"zm_village_fire_safety " +
                $"WHERE ST_Intersects(ST_SetSRID(geom, 4326), ST_Transform(ST_TileEnvelope( {z}, {x}, {y}), 4326))" +
                $" AND (is_delete<>1 or is_delete is null)) AS P";
            var tile = _dbContext.Database.GetDbConnection().Query<byte[]>(sql).FirstOrDefault();
            return tile;
        }
        //web端文件上传
        //public IActionResult UploadFile(string id)
        //{
        //    // 上传后的文件
        //    var filePost = HttpContext.Request.Form.Files[0];
        //    // 文件名
        //    string fileName = filePost.FileName;
        //    // 服务器上文件要保存的路径
        //    string TruePath = Directory.GetCurrentDirectory() + "/wwwroot/files/" + fileName;
        //    // 保存文件
        //    using (FileStream fs = System.IO.File.Create(TruePath))
        //    {
        //        filePost.CopyTo(fs);
        //    }
        //    // 返回文件在服务器上的路径
        //    return Ok(new { code = 0, msg = "", url = "http://localhost:49191/files/" + fileName });
        //}

    }
}
